﻿--project: Movie Site
--Team 3: Darya, Derrick, Olavo
CREATE PROCEDURE dbo.movieFilteredSearch (@filterType varchar(30), @searchString varchar(50))
--temporary parameter
AS
DECLARE
@genreID as int

IF(@filterType = null)
	BEGIN
	SELECT * FROM Movies
	WHERE movieTitle
	LIKE '%'+@searchString+'%'
	OR movieDesc
	LIKE '%'+@searchString+'%'
	OR productionStudio
	LIKE '%'+@searchString+'%'
	END
ELSE
	BEGIN
	IF(upper(@filterType) LIKE 'MOVIETITLE')
		BEGIN
		SELECT * FROM Movies
		WHERE movieTitle
		LIKE '%'+@searchString+'%'
		RETURN
		END

	IF(upper(@filterType) LIKE 'PRODUCTIONSTUDIO')
		BEGIN
		SELECT * FROM Movies
		WHERE productionStudio
		LIKE '%'+@searchString+'%'
		RETURN
		END

	IF(upper(@filterType) LIKE 'GENRE')
		BEGIN
		SET @genreID=(SELECT genreID FROM Genre WHERE upper(genreDesc) LIKE upper(@searchString))
		SELECT * FROM Movies
		WHERE genreID = @genreID
		RETURN
		END
	END
RETURN	